ARTIVA RM RECORD FORMATS

child pages:

page index:
Record Formats
Reccord Format Fields    
Queries to find which Record Format fields contain particular Table Fields and ultimately which File Format Sections and File Formats use them

Record Formats

2022-05-03
I created an import and using the STFILE queries below, only 1 entry was found (and it wasn't an actual row of the record format. This query did find them. Need to investigate
SELECT %NOLOCK
FSTRFFPOS AS FSTRFFPOS_POSITION_START,
FSTRFFNAME AS FSTRFFNAME_FIELD_NAME,
FSTRFFDESC AS FSTRFFDESC_DESCRIPTION,
FSTRFFID AS FSTRFFID_ID,
FSTRFFFILE AS FSTRFFFILE_RECORD_FORMAT_KEY,
DLDESC AS STFILE_DLDESC_DESCRIPTION,
DLTABLE AS STFILE_DLTABLE_TABLE,
DLLOGIC AS STFILE_DLLOGIC_RECORD_FORMAT_LOGIC_TABLE,
DLMEDIA AS STFILE_DLMEDIA_RECORD_FOMRAT_MEDIA_TYPE,
DLVAR AS STFILE_DLVAR_VARIABLE_OR_FIXED,
DLLIMIT AS STFILE_DLLIMIT_RECORD_FORMAT_DELIMITER,
FSTRFFTYPE AS FSTRFFTYPE_TYPE,
FSTRFFLEN AS FSTRFFLEN_LENGTH,
FSTRFFEND AS FSTRFFEND_END_POSITION,
FSTRFFFORMAT AS FSTRFFFORMAT_FORMAT,
FSTRFFMASK AS FSTRFFMASK_MASK_FORMAT
FROM %ALLINDEX FSTRFF              /* FSTRFF = "Temporary Record Format Fields" */
LEFT JOIN STFILE ON FSTRFFFILE = DLFILE /* STFILE = "Record Formats" */
WHERE FSTRFFFILE LIKE '%ZZII%'     /* FSTRFFFILE = "Record Format Key" aka "NAME" OF RECORD FORMAT */
ORDER BY FSTRFFFILE,FSTRFFPOS

TABLE NAME:        STFILE
TABLE DESC:         "Record Formats"
ELEMENT TYPE:   

-- DISPLAY RECORD FORMATS IN SAME DISPLAY ORDER AS IN RM RECORD FORMAT SCREEN
-- WITH EXCEPTION OF "MASK" COLUMN (AND MAYBE "END" COLUMN)
-- EASIER WAY IS TO JUST USE STFILE2_STFILE. WILL HAVE TO SEE WHAT OTHER INFO IS IN STFILE WHEN I HAVE TIME
SELECT
DLFNAME
-- ,FSTRFFFILE
,DLFDESC
,DLFTYPE
,DLFSTART
,DLFEND -- ONLY MEANINGFUL FOR FIXED LENGTH RECORD FORMATS
,DLFSPEC
,DLFLEN
,"next are stfile.*" as STFILE_SPLAT
,STFILE.*
,"next are stfile2_stfile.*" AS STFILE2_STFILE_SPLAT
,STFILE2_STFILE.*
--,FSTRFF.FSTRFFMASK -- **** NOT ***** THE "MASK" SHOWN ON THE RECORD FORMAT
FROM STFILE
INNER JOIN STFILE2_STFILE ON STFILE.DLFILE = STFILE2_STFILE.DLFILE
-- GIVES A TON OF RECORDS JOIN FSTRFF ON FSTRFFFILE = STFILE.DLFILE
--WHERE FSTRFFFILE = "Z[clientid]HBPY" --RECORD LAYOUT NAME
WHERE STFILE.DLFILE LIKE "[clientid]PBRL" -- RECORD FORMAT NAME
ORDER BY STFILE.DLFILE, STFILE2_STFILE.DLFSTART-- FIND BY NAME OR DESCRIPTION

SELECT * FROM STFILE WHERE
UPPER(DLDESC) LIKE UPPER("%ERE%")
OR
UPPER(DLFILE) LIKE UPPER("%ERE%")

-- FIND RECORD FORMATS WITH DESCRIPTION CONTAINING ...
SELECT * FROM STFILE WHERE UPPER(DLDESC) LIKE UPPER(%INVESTI%")

--FIND RECORD FORMATS NAMED ...
SELECT * FROM STFILE WHERE UPPER(DLFILE) LIKE UPPER("%ZNSINV%")

-- FIND ALL RECORD FORMATS WITH LOGIC BLOCK NAMED ...
SELECT * FROM STFILE WHERE UPPER(DLLOGIC) LIKE UPPER("%ZNSEREN%")

Record Format Fields

TABLE NAME:        STFILE2_STFILE
TABLE DESC:         "Record Format Fields"
ELEMENT TYPE:   

See comment for record format display in dbeaver [as of 2022-03-03 I don't know what this means]
Apparently this table cannot be used in a script(?)

-- DISPLAY RECORD FORMAT AS SHOWN IN RM
-- WORKS FOR VAR LEN FIELDS, MAY HAVE TO ADD END FOR FIXED LENGTH
SELECT
DLFKEY AS the_Index
,DLFNAME AS Field_Name
,DLFDESC AS Description
,DLFTYPE AS _Type
,CAST(DLFSTART AS INT) AS Postion_Start
,DLFSPEC AS Format
,"haven't found this one yet" as Mask_format
,DLFLEN AS BONUS_CHR_len
, *
FROM STFILE2_STFILE WHERE UPPER(DLFILE) = "ZZ[clientid]IN" -- NAME OF RECORD FORMAT OF INTEREST
ORDER BY DLFKEY

from nes
-- FIND THE FIELD DEFINITION INFO FOR THE [VAR WIDTH] ZNSINVAA RECORD FORMAT
SELECT
*
FROM STFILE2_STFILE
WHERE
UPPER(DLFILE) LIKE "%ZNSINVAA%"

Queries to find which Record Format fields contain particular Table Fields and ultimately which File Format Sections and File Formats use them

--QUERY 1) FIND ANY RECORD FORMAT FIELD THAT IS PRIM SEC TER OR QUAT
-- GROUP THEM TO FIND FIND WHICH RECORD FORMATS NEED TO BE CONSIDERED WHETHER PLACMENT OR NOT
-- COPY LIST IN COLUMN FSTRFFFILE_Record_Format_Key  TO INPUT TO QUERY 2
SELECT %NOLOCK
FSTRFFPOS,
FSTRFFID,
FSTRFFFILE AS FSTRFFFILE_Record_Format_Key,
FSTRFFNAME,
FSTRFFDESC,
DLDESC,
DLTABLE,
DLLOGIC,
DLMEDIA,
DLVAR,
FSTRFFTYPE,
FSTRFFLEN,
DLLIMIT,
FSTRFFEND,
FSTRFFFORMAT,
FSTRFFMASK
FROM %ALLINDEX FSTRFF   -- TABLE "Temporary Record Format Fields"
JOIN STFILE ON FSTRFFFILE = STFILE.DLFILE  -- STFILE = "RECORD FORMATS"  FSTRFFFILE= "Record Format Key"  STFILE.DLFILE= "Record Format KEY"
WHERE
/*NOT (
FSTRFFFILE LIKE '%ZI%'
OR
FSTRFFFILE LIKE '%ZX%')*/
(FSTRFFNAME LIKE "%AMPRIM%"
OR
FSTRFFNAME LIKE "%AMSEC%"
OR
FSTRFFNAME LIKE "%AMTER%"
OR
FSTRFFNAME LIKE "%AMQUAT%")
-- SINCE FILE FORMAT MUST END IN "PL", BUT RECORD FORMAT DOESN'T HAVE TO FOR A PLACEMENT DON'T ADD THIS CLAUSE -> AND FSTRFFFILE_Record_Format_Key LIKE "%PL"
GROUP BY FSTRFFFILE
ORDER BY FSTRFFFILE,FSTRFFPOS

--QUERY 2) FIND FILE FORMAT SECTION DETAIL RECORDS WHERE RECORD FORMAT IS IN RESULTS FROM QUERY 1 
-- THEN COPY THE RESULTS IN COLUMN FSFFSDFFSID_File_Format_Section_ID AS QUALIFIER FOR QUERY 3
-- NOTICE THAT ALL 34 ENTRIES HAVE FSFFSDORDER_order = 1; THIS MEANS THAT LATER WHEN INSPECTING THE
-- FILE FORMATS WE WILL ALWAYS BE LOOKING IN THE FILE FORMAT->GENERAL SECTION INFORMATION->DETAILS TABLE
-- FOR ENTRY WITH THE VALUE 1 IN THE "ORDER" COLUMN.  THIS IS THE SAME VALUE SHOWN IN THE
-- FILE FORMAT->GENERAL SECTION DETAIL INFORMATION->DETAIL ORDER FIELD
SELECT
FSFFSDID  -- KEY OF File Format Section Detail TABLE
,FSFFSDFFSID  AS         FSFFSDFFSID_File_Format_Section_ID
,FSFFSDORDER AS FSFFSDORDER_order
,FSFFSDRFSCRIPT AS FSFFSDRFSCRIPT_RECORD_SCRIPT
FROM FSFFSECDET
WHERE FSFFSDRFID IN ([FSTRFFFILE values from query 1])
GROUP BY FSFFSDFFSID /*File_Format_Section_ID*/
/*JOIN FSFFSECTION ON FSFFSECTION.FSFFSID = FSFFSECDET.FSFFSDFFSID
JOIN FSFILEFORMAT ON FSFILEFORMAT.FSFFID = FSFFSECTION.FSFFSFFID */

--QUERY 3  FIND THE FILE FORMATS THAT CONTAIN THE PRIM SEC TER QUAT AND ARE PLACEMENTS
SELECT
FSFFSID
,FSFFSSECTID AS FSFFSSECTID_section_id  -- ALL NULL
,FSFFSRECIDPOS 
,FSFFSFFID AS FSFFSFFID_FILE_FORMAT_ID
,FSFILEFORMAT.FSFFDESC AS FSFFDESC_FILE_FORMAT_DESC
FROM FSFFSECTION
JOIN FSFILEFORMAT ON FSFFSECTION.FSFFSFFID=FSFILEFORMAT.FSFFID
WHERE FSFFSID IN ([FSFFSDFFSID  values from query 2])  /*FSFFSID = KEY OF FILE FORMAT SECTION TABLE*/
/*-- UNCOMMENT THE LINE BELOW TO FIND ONLY PLACEMENTS
AND FSFFSFFID LIKE ("%PL") */

-- RESULTS FROM QUERY 3
FSFFSID column holds list of names of placement file formats

last updated:    Thu 2022-09-08 10:04 AM